<?php
class ChartsController extends AppController {

	var $name = 'Charts';
	var $helpers = array('flashChart');
	var $uses = array('Kqbanhang','Chitieunv','Zluykenv','Diemchuan');

	function thidu()
	{
		
	}
	
function luyketonghop($donvi_id=null)
	{
		if ($donvi_id!=null)
		{
			$nvkh = $this->Zluykenv->find('list',array(
												'recursive'=>0,
												'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
												'fields'=>array('nhanvien_id','nhanvien_id')));
			$nhanviens = $this->Chitieunv->Nhanvien->find('list',array('conditions'=>array('id'=>$nvkh),'fields'=>array('tennv')));
			/*
			 * set col luy ke
			 */
			$i=0;
			foreach ($nhanviens as $keynv =>$valuenv):
				$cols[$keynv][$keynv.'0']="Lũy Kế Tuần N";
	    		$cols[$keynv][$keynv.'1']="Kế Hoạch Tuần N";
	    		$cols[$keynv][$keynv.'2']="% Hoàn Thành Tuần";
	    		$cols[$keynv][$keynv.'3']="Kết Quả Tuần N-1";
	    		$cols[$keynv][$keynv.'4']="Lũy Kế Tháng";
	    		$cols[$keynv][$keynv.'5']="Kế Hoạch Tháng";
	    		$cols[$keynv][$keynv.'6']="% Hoàn Thành Tháng";
			endforeach;
			/*
			 * lay data
			 */
			$data =$this->Zluykenv->find('all',array('conditions'=>array('nhanvien_id'=>array_keys($nhanviens),)));
			if(!empty($data))
			{
			/*
			 * sap xep lai row hang hoa
			 */
			for ($i=0;$i<count($data);$i++)
	    	{
	    		$rows[$data[$i]['Hanghoa']['id']]=$data[$i]['Hanghoa']['tenhh'];
	    	}
	    	
	    	/*
			 * sap xep lai data
			 */
	    	$i=0;
	    	$tong =array();
	    	foreach ($nhanviens as $keynv =>$valuenv):
				foreach ($rows as $keyhh =>$valuehh)
		    	{
		    		$tong[$keyhh]=0;
		    		for($j=0;$j<count($data);$j++)
		    		{
		    			if ($data[$j]['Zluykenv']['nhanvien_id']==$keynv && $data[$j]['Zluykenv']['hanghoa_id']==$keyhh)
		    			{
		    				$luyketh[$keyhh][$keynv.'0']=$data[$j]['Zluykenv']['tuanN'];
		    				$luyketh[$keyhh][$keynv.'1']=$data[$j]['Zluykenv']['khtuan'];
		    				$luyketh[$keyhh][$keynv.'2']=round($data[$j]['Zluykenv']['hoanthanhtuan']).'%';
		    				$luyketh[$keyhh][$keynv.'3']=$data[$j]['Zluykenv']['tongN_1'];
		    				$luyketh[$keyhh][$keynv.'4']=$data[$j]['Zluykenv']['tuanN']+$data[$j]['Zluykenv']['tongN_1'];
		    				$luyketh[$keyhh][$keynv.'5']=$data[$j]['Zluykenv']['khthang'];
		    				$luyketh[$keyhh][$keynv.'6']=round($data[$j]['Zluykenv']['hoanthanhthang']).'%';
		    			}
		    		}
		    		
		    	}
	    	endforeach;
				foreach ($rows as $keyhh =>$valuehh)
		    	{
		    		$tong[$keyhh]=0;
		    		for($j=0;$j<count($data);$j++)
		    		{
		    			if ($data[$j]['Zluykenv']['hanghoa_id']==$keyhh)
		    			{
							$tong[$keyhh]=$tong[$keyhh]+ $data[$j]['Zluykenv']['tuanN']+$data[$j]['Zluykenv']['tongN_1'];		    				
		    			}
		    		}
		    		
		    	}
			}
			$ngayht = date('Y-m-d');
			$dc= $this->Diemchuan->find('all',array(
										'recursive'=>0,
										'conditions'=>array('Kehoach.donvi_id'=>$donvi_id,'Kehoach.ngaybd <='=>$ngayht,'Kehoach.ngaykt >='=>$ngayht),
										'fields'=>array('sum(Diemchuan.diem)' )));
			
	    	$str= "select luyke.nhanvien_id,luyke.tennv, sum(luyke.diemtuan) as diemtuan,sum(luyke.diemthang) as diemthang
	    			from (select Zluykenv.nhanvien_id,Nhanvien.tennv, Zluykenv.hanghoa_id,Diemchuan.diem as Diem,hoanthanhtuan,hoanthanhthang, Diemchuan.diem*hoanthanhtuan as diemtuan, Diemchuan.diem*hoanthanhthang as diemthang
	    			from zluykenvs as Zluykenv, diemchuans as Diemchuan,kehoaches as Kehoach,nhanviens as Nhanvien
	    			where Zluykenv.hanghoa_id=Diemchuan.hanghoa_id
	    				and  Zluykenv.nhanvien_id=Nhanvien.id
	    				and Diemchuan.kehoach_id=Kehoach.id
	    				and Nhanvien.donvi_id =".$donvi_id."
	    				and Kehoach.donvi_id= ".$donvi_id."
	    				and Kehoach.ngaybd <= '".$ngayht."'
	    				and Kehoach.ngaykt >= '".$ngayht."') luyke
	    			group by luyke.nhanvien_id";
	    	$result = $this->Zluykenv->query($str);
	    	
	    	$strmax = "select max(diem.diemtuan) as maxtuan ,max(diem.diemthang) as maxthang
	    			from (".$str.") diem";
	    	$maxs=$this->Zluykenv->query($strmax);
	    	$maxtuan = $maxs[0][0]['maxtuan'];
	    	$maxthang = $maxs[0][0]['maxthang'];
			$this->set(compact('result','maxtuan','maxthang'));
		}
		else
		{
			$this->redirect(array('controller'=>'pages','action'=>'home'));
		}
	}
}
